package com.loveyukari.dao.impl;

import com.loveyukari.common.Pager;
import com.loveyukari.dao.TourDao;
import com.loveyukari.model.TourUser;
import net.sf.json.JSON;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
public class JdbcTourDao extends BaseDao implements TourDao {
    @Override
    public void updateTourExclude(int id, int exclude) {
        String sql = "update tour set exclude = ? where id = ?";
        this.getJdbcTemplate().update(sql,exclude,id);
    }

    @Override
    public void insertTour(String douyuName, Integer authId, String phone, String qq, String token,String ip) {
        String sql = "insert into tour (douyu_name,auth_id,phone,qq,token,ip) values(?,?,?,?,?,?)";
        this.getJdbcTemplate().update(sql,douyuName,authId,phone,qq,token,ip);
    }

    @Override
    public List<TourUser> selectByCreateTime(String date) {
        String sql = "select * from tour where create_time >= ?";
        List<TourUser> list = this.getJdbcTemplate().query(sql, tourUserRowMapper, date);
        return list;
    }


    private RowMapper<TourUser> tourUserRowMapper = new RowMapper() {
        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            TourUser tourUser = new TourUser();
            tourUser.setId(resultSet.getInt("id"));
            tourUser.setToken(resultSet.getString("token"));
            tourUser.setQq(resultSet.getString("qq"));
            tourUser.setPhone(resultSet.getString("phone"));
            tourUser.setDouyuName(resultSet.getString("douyu_name"));
            tourUser.setAuthId(resultSet.getInt("auth_id"));
            tourUser.setIp(resultSet.getString("ip"));
            tourUser.setExclude(resultSet.getInt("exclude"));
            return tourUser;
        }
    };
    @Override
    public TourUser selectTourByPhone(String phone) {
        String sql = "select * from tour where phone = ?";
        List<TourUser> list = this.getJdbcTemplate().query(sql, tourUserRowMapper, phone);
        if (list.isEmpty()){
            return null;
        }
        return list.get(0);
    }

    @Override
    public TourUser selectTourByToken(String token) {
        String sql = "select * from tour where token = ?";
        List<TourUser> list = this.getJdbcTemplate().query(sql, tourUserRowMapper, token);
        if (list.isEmpty()){
            return null;
        }
        return list.get(0);
    }

    @Override
    public TourUser selectTourById(int id) {
        String sql = "select * from tour where id = ?";
        List<TourUser> list = this.getJdbcTemplate().query(sql, tourUserRowMapper, id);
        if (list.isEmpty()){
            return null;
        }
        return list.get(0);
    }

    @Override
    public List<TourUser> selectAll() {
        String sql = "select * from tour";
        List<TourUser> list = this.getJdbcTemplate().query(sql, tourUserRowMapper);
        return list;
    }

    @Override
    public List<JSONObject> selectByCity(String city) {
        String sql = "select t.*,ti.city,ti.status from tour t left join tour_item ti on t.id = ti.tour_id and ti.city = ?";
        List<JSONObject> list = this.getJdbcTemplate().query(sql, new RowMapper<JSONObject>() {
            @Override
            public JSONObject mapRow(ResultSet resultSet, int i) throws SQLException {
                JSONObject json = new JSONObject();
                json.put("id",resultSet.getInt("id"));
                json.put("qq",resultSet.getString("qq"));
                json.put("phone",resultSet.getString("phone"));
                json.put("douyuName",resultSet.getString("douyu_name"));
                json.put("authId",resultSet.getInt("auth_id"));
                String c = resultSet.getString("city");
                if (c == null){
                    c = city;
                }
                json.put("city",c);
                Object s = resultSet.getObject("status");
                int status = -1;
                if (s != null){
                    status = Integer.parseInt(s.toString());
                }
                json.put("status",status);
                json.put("exclude",resultSet.getInt("exclude"));
                return json;
            }
        }, city);
        return list;
    }

    @Override
    public void updateTicketNo(Integer tourId, String city,String ticketNo) {
        String sql = "update tour_item set ticket_no = ? where tour_id = ? and city = ?";
        this.getJdbcTemplate().update(sql,ticketNo,tourId,city);
    }

    @Override
    public Map<Integer,Integer> selectDraw(String city,Integer status) {
        String sql = "select t.auth_id,ti.tour_id from (select tour_id from tour_item where city = ? and status = ?) ti inner join tour t on ti.tour_id = t.id and t.exclude = 0 left join ban_auth ba on ba.status = 1 and ba.end_time > now() and t.auth_id = ba.auth_id where ba.id is null";
        Map<Integer, Integer> map = this.getJdbcTemplate().query(sql, new ResultSetExtractor<Map<Integer, Integer>>() {
            @Override
            public Map<Integer, Integer> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                HashMap<Integer, Integer> map = new HashMap<>();
                while (resultSet.next()){
                    int authId = resultSet.getInt("auth_id");
                    int tourId = resultSet.getInt("tour_id");
                    map.put(authId,tourId);
                }
                return map;
            }
        },city,status);
        return map;
    }

    @Override
    public void updateLastLoginTime(Integer tourId, String city) {
        String sql = "update tour_item set last_login_time = now() where tour_id = ? and city = ?";
        this.getJdbcTemplate().update(sql,tourId,city);
    }


    @Override
    public Integer selectTourIdByAuthId(int authId) {
        String sql = "select id from tour where auth_id = ?";
        List<Integer> list = this.getJdbcTemplate().queryForList(sql, Integer.class, authId);
        if (list.isEmpty()){
            return null;
        }
        return list.get(0);
    }

    @Override
    public String selectTicketNo(Integer tourId, String city) {
        String sql = "select ticket_no from tour_item where tour_id = ? and city = ?";
        List<String> list = this.getJdbcTemplate().queryForList(sql, String.class, tourId, city);
        if (list.isEmpty()){
            return null;
        }
        return list.get(0);
    }

    @Override
    public Integer selectTourItemStatus(Integer tourId, String city) {
        String sql = "select status from tour_item where tour_id = ? and city = ?";
        List<Integer> list = this.getJdbcTemplate().queryForList(sql, Integer.class, tourId, city);
        if (list.isEmpty()){
            return null;
        }
        return list.get(0);
    }

    @Override
    public void insertTourItem(Integer tourId, String city) {
        String sql = "insert into tour_item (tour_id,city,update_time,last_login_time) values (?,?,now(),now())";
        this.getJdbcTemplate().update(sql,tourId,city);
    }


    @Override
    public void updateTourItemStatus(Integer tourId, String city,Integer status) {
        if (status == null){
            status = -1;
        }
        String sql = "update tour_item set status = ?,update_time = now() where tour_id = ? and city = ?";
        this.getJdbcTemplate().update(sql,status,tourId,city);
    }

    @Override
    public JSONObject selectTourItemByTicketNo(String ticketNo) {
        String sql = "select * from tour_item where ticket_no = ?";
        List<JSONObject> list = this.getJdbcTemplate().query(sql, new RowMapper<JSONObject>() {
            @Override
            public JSONObject mapRow(ResultSet resultSet, int i) throws SQLException {
                JSONObject json = new JSONObject();
                json.put("tourId",resultSet.getInt("tour_id"));
                json.put("city",resultSet.getString("city"));
                json.put("status",resultSet.getInt("status"));
                json.put("ticketNo",resultSet.getString("ticket_no"));
                return json;
            }
        }, ticketNo);
        if (list.isEmpty()) {
            return null;
        }
        return list.get(0);
    }

    @Override
    public JSONObject selectTicketCode(String ticketCode) {
        String sql = "select * from ticket_code where ticket_code = ?";
        List<JSONObject> list = this.getJdbcTemplate().query(sql, new RowMapper<JSONObject>() {
            @Override
            public JSONObject mapRow(ResultSet resultSet, int i) throws SQLException {
                JSONObject json = new JSONObject();
                json.put("ticketCode", resultSet.getString("ticket_code"));
                json.put("city", resultSet.getString("city"));
                json.put("status", resultSet.getString("status"));
                return json;
            }
        }, ticketCode);
        if (list.isEmpty()){
            return null;
        }
        return list.get(0);
    }

    @Override
    public void updateTicketCode(String ticketCode) {
        String sql = "update ticket_code set status = 1, check_time = now() where ticket_code = ?";
        this.getJdbcTemplate().update(sql,ticketCode);
    }

    @Override
    public void insertTicketCode(String ticketCode, String city) {
        String sql = "insert into ticket_code (ticket_code,city) values(?,?)";
        this.getJdbcTemplate().update(sql,ticketCode,city);
    }


}
